package com.hlw.hr_man.daoImpl;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.management.StringValueExp;

import com.hlw.hr_man.Model.EmployeeModel;
import com.hlw.hr_man.dao.EmployeeDao;
import com.hlw.hr_man.util.JDBCUtil;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

/**
 * 员工表的dao层实现
 * 
 * @author Administrator
 *
 */
public class EmployeeDaoImpl implements EmployeeDao {
	/**
	 * 通过员工号和密码找到员工id
	 */
	@Override
	public EmployeeModel findEmpByEnumAndEpwd(String enumber, String epwd) {
		EmployeeModel employee=new EmployeeModel();
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select e_id,e_number,e_password,e_name,role_id,e_sex,e_age,e_tel,e_email,e_address,e_birthday,e_description from hr_employee where e_number=? and e_password=?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, enumber);
			pst.setString(2, epwd);
			ResultSet rs = pst.executeQuery();
			if (rs.next()) {
				employee.setE_name(rs.getString("e_name")) ;
				employee.setE_id(rs.getInt("e_id"));
				employee.setE_number(rs.getString("e_number"));
				employee.setRole_id(rs.getInt("role_id"));
				employee.setE_sex(rs.getString("e_sex"));
				employee.setE_age(rs.getInt("e_age"));
				employee.setE_tel(rs.getString("e_tel"));
				employee.setE_email(rs.getString("e_email"));
				employee.setE_address(rs.getString("e_address"));
				employee.setE_birthday(rs.getString("e_birthday"));
				employee.setE_description(rs.getString("e_description"));
				employee.setE_password(rs.getString("e_password"));
			}
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return employee;
	}

	/**
	 * 得到所有员工的总数
	 */
	@Override
	public int getEmployeeCount() {
		// TODO Auto-generated method stub
		int ecount = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(e_id) as ecount from hr_employee");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			ResultSet rs = pst.executeQuery();
			if (rs.next()) {
				ecount = rs.getInt("ecount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return ecount;
	}

	/**
	 * 按员工名字得到所有员工的总数（模糊查询）
	 */
	@Override
	public int getEmployeeCount(String searchname) {
		// TODO Auto-generated method stub
		int ecount = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(e_id) as ecount from hr_employee where e_name like ?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, "%" + searchname + "%");
			ResultSet rs = pst.executeQuery();
			if (rs.next()) {
				ecount = rs.getInt("ecount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return ecount;
	}

	/**
	 * 按部门名字得到所有员工的总数（重载模糊查询）
	 */
	@Override
	public int getEmployeeCount(String searchname, String searchtype) {
		// TODO Auto-generated method stub
		int ecount = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer(
				"select count(e_id) as ecount from hr_employee,hr_department where hr_department.dept_id=hr_employee.dept_id and dept_name like ?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, "%" + searchname + "%");
			ResultSet rs = pst.executeQuery();
			if (rs.next()) {
				ecount = rs.getInt("ecount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return ecount;
	}

	/**
	 * 获取当前员工信息
	 * 
	 * @param start
	 * @param pageSize
	 * @return 员工集合
	 */
	@Override
	public List<EmployeeModel> findAllEmployee(int start, int pageSize) {
		// TODO Auto-generated method stub
		List<EmployeeModel> employees = new ArrayList<EmployeeModel>();
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer(
				"select e_id,e_number,role_id,e_name,e_sex,e_age,e_tel,e_email,e_address,e_birthday,e_entrydate,e_description,hr_department.dept_name,hr_department.dept_id,card_id from hr_employee,hr_department where hr_department.dept_id=hr_employee.dept_id limit ?,?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, start);
			pst.setInt(2, pageSize);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				EmployeeModel employee = new EmployeeModel();
				employee.setE_id(rs.getInt("e_id"));
				employee.setE_number(rs.getString("e_number"));
				employee.setRole_id(rs.getInt("role_id"));
				employee.setE_name(rs.getString("e_name"));
				employee.setE_sex(rs.getString("e_sex"));
				employee.setE_age(rs.getInt("e_age"));
				employee.setE_tel(rs.getString("e_tel"));
				employee.setE_email(rs.getString("e_email"));
				employee.setE_address(rs.getString("e_address"));
				employee.setE_birthday(rs.getString("e_birthday"));
				employee.setE_entrydate(rs.getString("e_entrydate"));
				employee.setE_description(rs.getString("e_description"));
				employee.setDept_name(rs.getString("dept_name"));
				employee.setDept_id(rs.getInt("dept_id"));
				employee.setCard_id(rs.getInt("card_id"));
				employees.add(employee);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return employees;
	}

	/**
	 * 按名字获取当钱员工信息（模糊查询）
	 * 
	 * @param start
	 * @param pageSize
	 * @param searchname
	 * @return 员工集合
	 */
	@Override
	public List<EmployeeModel> findAllEmployee(int start, int pageSize, String searchname) {
		// TODO Auto-generated method stub
		List<EmployeeModel> employees = new ArrayList<EmployeeModel>();
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer(
				"select e_id,e_number,role_id,e_name,e_sex,e_age,e_tel,e_email,e_address,e_birthday,e_entrydate,e_description,hr_department.dept_name,hr_department.dept_id,card_id from hr_employee,hr_department where hr_department.dept_id=hr_employee.dept_id and e_name like ? limit ?,?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, "%" + searchname + "%");
			pst.setInt(2, start);
			pst.setInt(3, pageSize);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				EmployeeModel employee = new EmployeeModel();
				employee.setE_id(rs.getInt("e_id"));
				employee.setE_number(rs.getString("e_number"));
				employee.setRole_id(rs.getInt("role_id"));
				employee.setE_name(rs.getString("e_name"));
				employee.setE_sex(rs.getString("e_sex"));
				employee.setE_age(rs.getInt("e_age"));
				employee.setE_tel(rs.getString("e_tel"));
				employee.setE_email(rs.getString("e_email"));
				employee.setE_address(rs.getString("e_address"));
				employee.setE_birthday(rs.getString("e_birthday"));
				employee.setE_entrydate(rs.getString("e_entrydate"));
				employee.setE_description(rs.getString("e_description"));
				employee.setDept_name(rs.getString("dept_name"));
				employee.setDept_id(rs.getInt("dept_id"));
				employee.setCard_id(rs.getInt("card_id"));
				employees.add(employee);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return employees;
	}

	/**
	 * 按部门名字查询员工（重载模糊查询）
	 */
	@Override
	public List<EmployeeModel> findAllEmployee(int start, int pageSize, String searchname, String searchtype) {
		// TODO Auto-generated method stub
		List<EmployeeModel> employees = new ArrayList<EmployeeModel>();
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer(
				"select e_id,e_number,role_id,e_name,e_sex,e_age,e_tel,e_email,e_address,e_birthday,e_entrydate,e_description,hr_department.dept_name,hr_department.dept_id,card_id from hr_employee,hr_department where hr_department.dept_id=hr_employee.dept_id and dept_name like ? limit ?,?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, "%" + searchname + "%");
			pst.setInt(2, start);
			pst.setInt(3, pageSize);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				EmployeeModel employee = new EmployeeModel();
				employee.setE_id(rs.getInt("e_id"));
				employee.setE_number(rs.getString("e_number"));
				employee.setRole_id(rs.getInt("role_id"));
				employee.setE_name(rs.getString("e_name"));
				employee.setE_sex(rs.getString("e_sex"));
				employee.setE_age(rs.getInt("e_age"));
				employee.setE_tel(rs.getString("e_tel"));
				employee.setE_email(rs.getString("e_email"));
				employee.setE_address(rs.getString("e_address"));
				employee.setE_birthday(rs.getString("e_birthday"));
				employee.setE_entrydate(rs.getString("e_entrydate"));
				employee.setE_description(rs.getString("e_description"));
				employee.setDept_name(rs.getString("dept_name"));
				employee.setDept_id(rs.getInt("dept_id"));
				employee.setCard_id(rs.getInt("card_id"));
				employees.add(employee);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return employees;
	}

	/**
	 * 添加员工信息
	 */
	@Override
	public int insertEmployee(EmployeeModel emp) {
		// TODO Auto-generated method stub
		int flag = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer(
				"insert into hr_employee (e_number,role_id,e_name,e_sex,e_age,e_tel,e_email,e_address,e_birthday,e_entrydate,e_description,dept_id,e_password) values(?,?,?,?,?,?,?,?,?,?,?,?,?)");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, emp.getE_number());
			pst.setInt(2, emp.getRole_id());
			pst.setString(3, emp.getE_name());
			pst.setString(4, emp.getE_sex());
			pst.setInt(5, emp.getE_age());
			pst.setString(6, emp.getE_tel());
			pst.setString(7, emp.getE_email());
			pst.setString(8, emp.getE_address());
			pst.setString(9, emp.getE_birthday());
			pst.setString(10, emp.getE_entrydate());
			pst.setString(11, emp.getE_description());
			pst.setInt(12, emp.getDept_id());
			pst.setString(13, emp.getE_password());

			int i = pst.executeUpdate();
			if (i > 0) {
				flag = 1;
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	/**
	 * 得到员工总数
	 */
	@Override
	public int getEmployeesConut() {
		int ecount = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(e_id) ecount from  hr_employee ");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());

			ResultSet rs = pst.executeQuery();
			if (rs.next()) {
				ecount = rs.getInt("ecount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return ecount;
	}

	/**
	 * 得到员工信息
	 */
	@Override
	public List<EmployeeModel> getEmployees(int start, int pageSize) {
		List<EmployeeModel> employees = new ArrayList<EmployeeModel>();
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer(
				"select hr_employee.e_number,hr_employee.e_name,hr_department.dept_name,hr_employee.role_id,hr_role.role_name  from hr_role , hr_employee,hr_department where hr_employee.dept_id=hr_department.dept_id AND hr_role.role_id=hr_employee.role_id  ");
		sql.append(" limit ?,?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, start);
			pst.setInt(2, pageSize);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				EmployeeModel employee = new EmployeeModel(rs.getString("e_number"), rs.getString("e_name"),
						rs.getString("dept_name"), rs.getInt("role_id"),rs.getString("role_name"));

				employees.add(employee);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return employees;
	}

	/**
	 * 通过e_number得到 员工对象
	 */
	@Override
	public EmployeeModel getEmployeeByEnumber(String e_number) {
		// TODO Auto-generated method stub
		EmployeeModel employee = null;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select e_id,e_name from hr_employee where e_number=?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, e_number);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				employee = new EmployeeModel();
				employee.setE_id(rs.getInt("e_id"));
				employee.setE_name(rs.getString("e_name"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return employee;
	}
    
	/**
	 * 修改员工信息
	 */
	@Override
	public int updateEmployee(EmployeeModel emp) {
		// TODO Auto-generated method stub
		int flag = 0 ;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("update hr_employee set e_number=?,e_name=?,e_sex=?,e_age=?,e_tel=?,e_email=?,e_address=?,e_birthday=?,e_entrydate=?,e_description=?,dept_id=? where e_id=?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, emp.getE_number());
			pst.setString(2, emp.getE_name());
			pst.setString(3, emp.getE_sex());
			pst.setInt(4, emp.getE_age());
			pst.setString(5, emp.getE_tel());
			pst.setString(6, emp.getE_email());
			pst.setString(7, emp.getE_address());
			pst.setString(8, emp.getE_birthday());
			pst.setString(9, emp.getE_entrydate());
			pst.setString(10, emp.getE_description());
			pst.setInt(11, emp.getDept_id());
			pst.setInt(12, emp.getE_id());
			int i = pst.executeUpdate();
			if(i>0){
				flag = 1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	@Override
	public int updateRoleToNormal(String enumber) {
		int flag=0;
		Connection   con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("update hr_employee set role_id=2 where e_number=? ");
		try {
			PreparedStatement   pst =  con.prepareStatement(sql.toString());
			pst.setString(1, enumber);
			int i=pst.executeUpdate();
			if(i>0){
				flag=1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}


	@Override
	public int findEmployeeByRoleid(int roleid) {
		int flag = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select * from hr_employee where role_id=?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, roleid);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				 flag=1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
				
		return flag;
	}


	@Override
	public int updateRoleToManger(String e_number,int roleid) {
		int flag=0;
		Connection   con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("update hr_employee set role_id=? where e_number=? ");
		try {
			PreparedStatement   pst =  con.prepareStatement(sql.toString());
			pst.setInt(1, roleid);
			pst.setString(2, e_number);
			int i=pst.executeUpdate();
			if(i>0){
				flag=1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	

	/**
	 * 删除员工信息
	 */
	@Override
	public int deleteEmployees(String[] e_id) {
		// TODO Auto-generated method stub
		int flag = 0;
		int a = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("delete from hr_employee where hr_employee.e_id=?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			for (int i = 0; i < e_id.length; i++) {
				pst.setInt(1, Integer.parseInt(e_id[i]));
				a = pst.executeUpdate();
			}
			if(a>0){
				flag = 1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public int updateMyInfo(EmployeeModel employee) {
		int flag = 0 ;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("update hr_employee set e_sex=?,e_age=?,e_tel=?,e_email=?,e_address=?,e_birthday=?,e_description=? where e_number=?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, employee.getE_sex());
			pst.setInt(2, employee.getE_age());
			pst.setString(3, employee.getE_tel());
			pst.setString(4, employee.getE_email());
			pst.setString(5, employee.getE_address());
			pst.setString(6, employee.getE_birthday());
			pst.setString(7, employee.getE_description());
			pst.setString(8, employee.getE_number());
			int i = pst.executeUpdate();
			if(i>0){
				flag = 1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public int upatePassword(String password, int e_id) {
		int flag=0;
		Connection con=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("update hr_employee set e_password=? where e_id=?");
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setString(1, password);
			pst.setInt(2, e_id);
			int i=pst.executeUpdate();
			if(i>0){
				flag=1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return flag;
	}
/**
	 * 导入文件加载员工信息
	 */
	@Override
	public boolean uploadEmployees(String path) {
		// TODO Auto-generated method stub
		boolean flag = false;
		Workbook wbk = null;
		File file = new File(path);
		try {
			InputStream is = new FileInputStream(file);   //创建文件输入流
			wbk = Workbook.getWorkbook(is);               //解析流
			Sheet sh = wbk.getSheet(0);                  //表的第一个sheet
			for (int i = 1; i < sh.getRows(); i++) {
				String e_number = sh.getCell(0, i).getContents();
				String e_password = sh.getCell(1,i).getContents();
				String e_name = sh.getCell(2,i).getContents();
				String e_sex = sh.getCell(3,i).getContents();
				String e_age = sh.getCell(4,i).getContents();
				String e_tel = sh.getCell(5,i).getContents();
				String e_email = sh.getCell(6,i).getContents();
				String e_address = sh.getCell(7,i).getContents();
				String e_birthday = sh.getCell(8,i).getContents();
				String e_entrydate = sh.getCell(9,i).getContents();
				String e_description = sh.getCell(10,i).getContents();
				String dept_id = sh.getCell(11,i).getContents();
				
				Connection con = JDBCUtil.getConnection();
				StringBuffer sql = new StringBuffer("insert into hr_employee (e_number,e_password,e_name,e_sex,e_age,e_tel,e_email,e_address,e_birthday,e_entrydate,e_description,dept_id,role_id) values(?,?,?,?,?,?,?,?,?,?,?,?,2)");
				PreparedStatement pst = con.prepareStatement(sql.toString());
					pst.setString(1, e_number);
					pst.setString(2, e_password);
					pst.setString(3, e_name);
					pst.setString(4, e_sex);
					pst.setInt(5, Integer.parseInt(e_age));
					pst.setString(6, e_tel);
					pst.setString(7, e_email);
					pst.setString(8, e_address);
					pst.setString(9, e_birthday);
					pst.setString(10, e_entrydate);
					pst.setString(11, e_description);
					pst.setInt(12, Integer.parseInt(dept_id));
					int a = pst.executeUpdate();
					if(a>0){
						flag = true;
					}
					else{
						flag = false;
						break;
					}
			  JDBCUtil.closeJDBC(pst, con);
			}
			wbk.close();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
     
	/**
	 * 验证员工编号是否存在
	 */
	@Override
	public int validateE_number(String e_number) {
		// TODO Auto-generated method stub
		int flag = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select e_name from hr_employee where e_number=?");
        try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, e_number);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				flag = 1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
		return flag;
	}
}



	